﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using GPCSC.Webs;
using System.Data;
using System.Collections;
using System.Data.SqlClient;
using System.Configuration;
using ExtAspNet;

namespace GPCSC.Web
{
    public partial class sqle : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
           
            if (!IsPostBack)
            {
               Loadda();
            }
        }

        #region 初始化树
        //database
        protected void Loadda()
        {
            //查询 数据库
            DataSet ds = dasql("SELECT name FROM master..sysdatabases WHERE name NOT IN('master','model','msdb','tempdb','northwind','pubs')");
          DropDownList1.DataSource = ds;
           DropDownList1.DataBind();
            //if (ds != null)
            //{
            //    if (ds.Tables.Count > 0)
            //    {
            //        if (ds.Tables[0].Rows.Count > 0)
            //        {

            //            foreach (DataRow dr in ds.Tables[0].Rows)
            //            {
            //                ExtAspNet.TreeNode node = new ExtAspNet.TreeNode();
            //                node.Text = dr["name"].ToString();
            //                //node.NodeID = dr["name"].ToString();
            //                node.Expanded = true;
            //                node.Icon =  ExtAspNet.Icon.Add;
            //                datatable(dr["name"].ToString(), node);
            //                treeMenu.Nodes.Add(node);
                           
            //            }
            //        }
            //    }
            //}
           //GridView1.DataSource = ds;
           //GridView1.DataBind();

           
        }
        protected void datatable(string databaseName , ExtAspNet.TreeNode node)
        { 
            //SELECT Name FROM SJSB..SysObjects Where XType='U' ORDER BY Name
            DataSet ds = dasql("SELECT Name FROM " + databaseName + "..SysObjects Where XType='U' ORDER BY Name");
            if (ds != null)
            {
                if (ds.Tables.Count > 0)
                {
                    if (ds.Tables[0].Rows.Count > 0)
                    {

                        foreach (DataRow dr in ds.Tables[0].Rows)
                        {
                            ExtAspNet.TreeNode nodes = new ExtAspNet.TreeNode();
                            nodes.Text = dr["name"].ToString();
                            //nodes.NodeID = dr["name"].ToString();
                            nodes.Expanded = true;
                            nodes.Icon = ExtAspNet.Icon.Pencil;
                            datacol(databaseName, dr["name"].ToString(), nodes);
                            node.Nodes.Add(nodes);

                        }
                    }
                }
            }
        }

        protected void datacol(string database, string datable,ExtAspNet.TreeNode node)
        { 
       // use[DWeb]  SELECT sc.name fieldName,st.name typeName,st.length length FROM syscolumns sc LEFT JOIN systypes st ON sc.xtype=st.xtype WHERE st.name NOT IN('sysname') AND sc.id=object_id('AnswerD')
            DataSet ds = dasql("use[" + database + "]  SELECT sc.name fieldName,st.name typeName,st.length length FROM syscolumns sc LEFT JOIN systypes st ON sc.xtype=st.xtype WHERE st.name NOT IN('sysname') AND sc.id=object_id('" + datable + "')");
            if (ds != null)
            {
                if (ds.Tables.Count > 0)
                {
                    if (ds.Tables[0].Rows.Count > 0)
                    {

                        foreach (DataRow dr in ds.Tables[0].Rows)
                        {
                            ExtAspNet.TreeNode nodes = new ExtAspNet.TreeNode();
                            nodes.Text = dr["fieldName"].ToString() + "(" + dr["typeName"].ToString() + "(" + dr["length"].ToString() + "))";
                            //nodes.NodeID = dr["name"].ToString();
                            nodes.Expanded = true;
                           // nodes.Icon = ExtAspNet.Icon.Pencil;

                            node.Nodes.Add(nodes);

                        }
                    }
                }
            }
        }
        #endregion
        protected DataSet dasql(string sql)
        {
            DataSet ds = new DataSet();
            using (SqlConnection connection = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"].ToString()))
            {
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, connection, sql);
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    //DataSet ds = new DataSet();
                    try
                    {
                        da.Fill(ds, "ds");
                        cmd.Parameters.Clear();
                    }
                    catch (System.Data.SqlClient.SqlException ex)
                    {
                        throw new Exception(ex.Message);
                    }
                    finally
                    {
                        cmd.Dispose();
                        connection.Close();
                    }
                    
                }
            }
            return ds;
        }
        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn,  string cmdText)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;

            cmd.CommandType = CommandType.Text;//cmdType;
        }

        //protected void Button1_Click(object sender, EventArgs e)
        //{
        //    //string databasename = DropDownList1.SelectedValue;
        //    string sdf = ids.Value;
        //    try
        //    {
        //        //sdf = "use[" + databasename + "] " + sdf;
        //        DataSet ds = dasql(sdf);
        //        //GridView1.DataSource = ds;
        //        //GridView1.DataBind();
        //    }
        //    catch (Exception ee)
        //    {
        //        Alert.Show(ee.ToString());
        //    }
        //}

        //protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
        //{
        //    string databasename = DropDownList1.SelectedValue;
        //    treeMenu.Nodes.Clear
        //    ExtAspNet.TreeNode node = new ExtAspNet.TreeNode();
        //    node.Text = databasename;
           
        //    node.Expanded = true;
        //    node.Icon = ExtAspNet.Icon.Add;
        //    datatable(databasename, node);
        //    treeMenu.Nodes.Add(node);
        //}
        protected void Button1_Click(object sender, EventArgs e)
        {
            //string databasename = DropDownList1.SelectedValue;
            //string sdf = ids.Value;
            //try
            //{
            //    sdf = "use[" + databasename + "] " + sdf;
            //    DataSet ds = dasql(sdf);
            //    GridView1.DataSource = ds;
            //    GridView1.DataBind();
            //}
            //catch (Exception ee)
            //{
            //    Alert.Show(ee.ToString());
            //}
            
        }
        protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
        {
                string databasename = DropDownList1.SelectedValue;
              //  ids.Value = databasename;
                Label1.Text = databasename;
               
            treeMenu.Nodes.Clear();
                ExtAspNet.TreeNode node = new ExtAspNet.TreeNode();
                node.Text = databasename;
                
                node.Expanded = true;
                node.Icon = ExtAspNet.Icon.Add;
                datatable(databasename, node);
                treeMenu.Nodes.Add(node);
        }

    }
}